CREATE TABLE buckets (
  id SERIAL PRIMARY KEY,
  rect BOX2D NOT NULL,
  parent_id int NOT NULL default -1
);

INSERT INTO buckets (
    rect)
VALUES (
    ST_MakeBox2D(ST_MakePoint(-180., -90.), ST_MakePoint(180., 90.))
);

CREATE TABLE nodes (
  osm_id int PRIMARY KEY,
  bucket_id int NOT NULL DEFAULT -1,
  geodata GEOGRAPHY(POINT, 4326) NOT NULL
);

CREATE TABLE ways (
  id SERIAL PRIMARY KEY,
  src int NOT NULL REFERENCES nodes(osm_id) ON DELETE CASCADE ON UPDATE CASCADE,
  dst int NOT NULL REFERENCES nodes(osm_id) ON DELETE CASCADE ON UPDATE CASCADE,
  weight float NOT NULL DEFAULT -1
);

CREATE INDEX idx_nodes_gist 
ON nodes
USING GIST (
  geodata
);

CREATE INDEX idx_nodes_buckets 
ON nodes(
  bucket_id
);

CREATE OR REPLACE FUNCTION SplitBucket(
  b_id int) 
RETURNS int AS $$
DECLARE
  old_x_dist float;
  old_y_dist float;
  new_x_dist float; 
  new_y_dist float;  
  new_id int;
  old_rect BOX2D;
  new_rect BOX2D;
BEGIN
  SELECT rect 
  FROM buckets 
  WHERE id = b_id
  INTO old_rect;

  old_y_dist = ST_YMax(old_rect) - ST_YMin(old_rect);
  old_x_dist = ST_XMax(old_rect) - ST_XMin(old_rect);
  new_y_dist = old_y_dist / 2.;
  new_x_dist = old_x_dist / 2.;
  
  -- Upper left
  new_rect = ST_MakeBox2D(
    ST_Point(ST_XMin(old_rect), ST_YMin(old_rect) + new_y_dist),
    ST_Point(ST_XMin(old_rect) + new_x_dist, ST_YMax(old_rect)));
  INSERT INTO buckets (
    rect)    
  VALUES (
    new_rect
  ) 
  RETURNING id 
  INTO new_id;

  UPDATE nodes SET
    bucket_id = new_id
  WHERE
    bucket_id = b_id AND ST_WITHIN(geodata::geometry, ST_SetSRID(new_rect::geometry, 4326));  
  -- Upper right
  new_rect = ST_MakeBox2D(
    ST_Point(ST_XMin(old_rect) + new_x_dist, ST_YMin(old_rect) + new_y_dist),
    ST_Point(ST_XMax(old_rect), ST_YMax(old_rect)));
  INSERT INTO buckets (
    rect)    
  VALUES (
    new_rect
  ) 
  RETURNING id 
  INTO new_id;

  UPDATE nodes SET
    bucket_id = new_id
  WHERE
    bucket_id = b_id AND ST_WITHIN(geodata::geometry, ST_SetSRID(new_rect::geometry, 4326));
  -- Lower left
  new_rect = ST_MakeBox2D(
    ST_Point(ST_XMin(old_rect), ST_YMin(old_rect)),
    ST_Point(ST_XMin(old_rect) + new_x_dist, ST_YMin(old_rect) + new_y_dist));
  INSERT INTO buckets (
    rect)    
  VALUES (
    new_rect
  ) 
  RETURNING id 
  INTO new_id;

  UPDATE nodes SET
    bucket_id = new_id
  WHERE
    bucket_id = b_id AND ST_WITHIN(geodata::geometry, ST_SetSRID(new_rect::geometry, 4326));
  -- Lower right
  new_rect = ST_MakeBox2D(
    ST_Point(ST_XMin(old_rect) + new_x_dist, ST_YMin(old_rect)),
    ST_Point(ST_XMax(old_rect), ST_YMin(old_rect) + new_y_dist));
  INSERT INTO buckets (
    rect)    
  VALUES (
    new_rect
  ) 
  RETURNING id 
  INTO new_id;

  UPDATE nodes SET
    bucket_id = new_id
  WHERE
    bucket_id = b_id AND ST_WITHIN(geodata::geometry, ST_SetSRID(new_rect::geometry, 4326));
  -- Dummy return statement
  RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
